package com.scalar.chiptrack.sales.parser;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.commons.Address;

import java.util.ArrayList;
import java.util.Iterator;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

/**
 * User: Sujan
 * Date: Jan 10, 2007
 * Time: 4:30:24 PM
 */
public final class SalesParserDAO
{
    private SalesParserDAO()
    {
    }

    public static void saveSalesOrders ( DBTransaction dbTransaction, ArrayList<SalesParserLineItem> lineItemList ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        int lineItemListSize = (lineItemList == null ) ? 0 : lineItemList.size();
        SalesParserLineItem lineItem = null;
        int portingStatusCode = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "DELETE FROM Customer_Pending_Order_LineItem" );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt =null;

            cstmt = conn.prepareCall ( "{CALL SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}" );
            for ( int i=0; i < lineItemListSize; i++ )
            {
                lineItem = (SalesParserLineItem)lineItemList.get(i);
                if ( lineItem.getPortingStatusCode() != -1 )
                {
                    cstmt.setString ( 1, lineItem.getSalesOrderNo() );
                    cstmt.setString ( 2, lineItem.getCustomerNumber() ); //
                    cstmt.setString ( 3, (lineItem.getCustomerPO() == null || lineItem.getCustomerPO().trim().length() == 0) ? lineItem.getSalesOrderNo() : lineItem.getCustomerPO() );
                    cstmt.setString ( 4, lineItem.getShippingMethod() );
                    cstmt.setString ( 5, lineItem.getDeliveryTerms() );
                    cstmt.setInt ( 6, lineItem.getLineItemNo() );
                    cstmt.setInt ( 7, lineItem.getShipmentNo() );
                    cstmt.setString ( 8, lineItem.getManufacturingPartNo() ); //
                    cstmt.setInt ( 9, lineItem.getQuantity() );
                    cstmt.setString ( 10, StringUtils.formatAmount( lineItem.getUnitPrice(), "##########.###") );
                    cstmt.setString( 11, lineItem.getRequestedDockDate() );
                    cstmt.setString ( 12, (lineItem.getScheduleDate() == null || lineItem.getScheduleDate().trim().length() == 0 ) ? null : lineItem.getScheduleDate().trim() );
                    cstmt.setString ( 13, (lineItem.getBookingDate() == null || lineItem.getBookingDate().trim().length() == 0 ) ? null : lineItem.getBookingDate().trim() );

                    cstmt.setString ( 14, lineItem.getCustomerPartNo() );
                    cstmt.setString ( 15, lineItem.getShippingLocation() );
                    cstmt.setString ( 16, lineItem.getEndUser() );
                    cstmt.setString( 17, lineItem.getEndCustomer() );
                    cstmt.setString ( 18, lineItem.getTransactionDate() );
                    cstmt.setString ( 19, lineItem.getCustomerPORev() );
                    cstmt.setString ( 20, lineItem.getPaymentTerms() );
                    cstmt.setString( 21, (lineItem.getRevisedScheduleDate() == null || lineItem.getRevisedScheduleDate().trim().length() == 0 ) ? null : lineItem.getRevisedScheduleDate().trim() );

                    cstmt.registerOutParameter( 22, Types.INTEGER );
                    cstmt.setString( 23, lineItem.getSalesPerson() );
                    cstmt.setInt ( 24, lineItem.getIsSample() );
                    cstmt.setString ( 25, (lineItem.getShippedDate() == null || lineItem.getShippedDate().trim().length() == 0 ) ? null : lineItem.getShippedDate().trim() );
                    cstmt.setString ( 26, lineItem.getDescription() );
                    Address shipAddress = (lineItem.getShipAddress() == null ? new Address() : lineItem.getShipAddress());
                    cstmt.setString ( 27, shipAddress.getAddress1() );
                    cstmt.setString ( 28, shipAddress.getAddress2() );
                    cstmt.setString ( 29, shipAddress.getAddress3() );
                    cstmt.setString ( 30, shipAddress.getCity() );
                    cstmt.setString ( 31, shipAddress.getState() );
                    cstmt.setString ( 32, shipAddress.getZipCode() );
                    cstmt.setString ( 33, shipAddress.getCountry() );
                    cstmt.setString ( 34, lineItem.getCustomerName() );
                    cstmt.setString ( 35, lineItem.getRegion() );
                    cstmt.setString ( 36, lineItem.getStatus() );
                    cstmt.setInt ( 37, lineItem.getInternalPOLineNo() );
                    cstmt.setString ( 38, lineItem.getMarketingPartNo() );
                    cstmt.setInt ( 39, lineItem.isPendingShipment() ? 0 : 1 );
                    cstmt.setString( 40, lineItem.getShipReqNo());
                    cstmt.execute();
                    portingStatusCode = cstmt.getInt( 22 );
                    lineItem.setPortingStatusCode( portingStatusCode );
                }
            }

            cstmt.close();
            cstmt = null;
            //LINE ITEMS WHICH ARE NOT UPDATED TODAY SETTING TO SHIPPED AS WE ARE MAINTAINING SNAPSHOT OF THE SHEET
            pstmt = conn.prepareStatement ( "UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'SHIPPED' WHERE CONVERT(VARCHAR, [TIMESTAMP], 101) != CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)" );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt =null;

            //APPROVING THE SHIPPED LINE ITEMS
            pstmt = conn.prepareStatement ( "UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '', IS_APPROVED = 0 WHERE LINEITEM_ID IN (SELECT LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE CONVERT(VARCHAR, [TIMESTAMP], 101) != CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101))" );
            pstmt.executeUpdate();

            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not save sales orders", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( !success )
                {
                    try{ conn.rollback(); } catch ( SQLException ignore ){}
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
    }

    public static void saveSalesOrdersSiTime ( DBTransaction dbTransaction, ArrayList<SalesParserLineItem> lineItemList ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        int lineItemListSize = (lineItemList == null ) ? 0 : lineItemList.size();
        SalesParserLineItem lineItem = null;
        int portingStatusCode = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall ( "{CALL SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM_SITIME(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}" );

            for ( int i=0; i < lineItemListSize; i++ )
            {
                lineItem = (SalesParserLineItem)lineItemList.get(i);
                if ( lineItem.getPortingStatusCode() != -1 )
                {
                    cstmt.setString ( 1, lineItem.getSalesOrderNo() );
                    cstmt.setString ( 2, lineItem.getCustomerName() ); //
                    cstmt.setString ( 3, (lineItem.getCustomerPO() == null || lineItem.getCustomerPO().trim().length() == 0) ? lineItem.getSalesOrderNo() : lineItem.getCustomerPO() );
                    cstmt.setString ( 4 , lineItem.getPaymentTerms() );
                    cstmt.setString ( 5, lineItem.getShippingMethod() );
                    cstmt.setInt ( 6, lineItem.getLineItemNo() );
                    cstmt.setString ( 7, lineItem.getManufacturingPartNo() ); //
                    cstmt.setInt ( 8, lineItem.getQuantity() );
                    cstmt.setString ( 9, StringUtils.formatAmount( lineItem.getUnitPrice(), "##########.####") );
                    cstmt.setString( 10, lineItem.getRequestedDockDate() );
                    cstmt.setString ( 11, (lineItem.getScheduleDate() == null || lineItem.getScheduleDate().trim().length() == 0 ) ? null : lineItem.getScheduleDate().trim() );
                    cstmt.setString ( 12, (lineItem.getBookingDate() == null || lineItem.getBookingDate().trim().length() == 0 ) ? null : lineItem.getBookingDate().trim() );
                    cstmt.setString( 13, lineItem.getEndCustomer() );
                    cstmt.registerOutParameter( 14, Types.INTEGER );
                    Address shipAddress = (lineItem.getShipAddress() == null ? new Address() : lineItem.getShipAddress());
                    cstmt.setString ( 15, shipAddress.getAddress1() );
                    cstmt.setString ( 16, shipAddress.getAddress2() );
                    cstmt.setString ( 17, shipAddress.getAddress3() );
                    cstmt.setString ( 18, shipAddress.getAddress4() );
                    cstmt.setString ( 19, shipAddress.getCity() );
                    cstmt.setString ( 20, shipAddress.getState() );
                    cstmt.setString ( 21, shipAddress.getZipCode() );
                    cstmt.setString ( 22, shipAddress.getCountry() );
                    cstmt.setString ( 23, shipAddress.getContactPerson() );
                    cstmt.setString ( 24, shipAddress.getPhoneNumber() );
                    Address billingAddress = (lineItem.getBillingAddress() == null ? new Address() : lineItem.getBillingAddress() );
                    cstmt.setString ( 25, billingAddress.getAddress1() );
                    cstmt.setString ( 26, billingAddress.getAddress2() );
                    cstmt.setString ( 27, billingAddress.getAddress3() );
                    cstmt.setString ( 28, billingAddress.getAddress4() );
                    cstmt.setString ( 29, billingAddress.getCity() );
                    cstmt.setString ( 30, billingAddress.getState() );
                    cstmt.setString ( 31, billingAddress.getZipCode() );
                    cstmt.setString ( 32, billingAddress.getCountry() );
                    cstmt.setString ( 33, billingAddress.getContactPerson() );
                    cstmt.setString ( 34, billingAddress.getPhoneNumber() );
                    cstmt.setString ( 35, lineItem.getStatus() );
                    cstmt.setString ( 36, shipAddress.getTagName());
                    cstmt.setString ( 37, billingAddress.getTagName());
                    cstmt.setString ( 38, lineItem.getCustomerNumber() );
                    cstmt.execute();
                    portingStatusCode = cstmt.getInt( 14 );
                    lineItem.setPortingStatusCode( portingStatusCode );
                }
            }

            cstmt.close();
            cstmt = null;
            //LINE ITEMS WHICH ARE NOT UPDATED TODAY SETTING TO SHIPPED AS WE ARE MAINTAINING SNAPSHOT OF THE SHEET
            pstmt = conn.prepareStatement ( "UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'SHIPPED' WHERE CONVERT(VARCHAR, [TIMESTAMP], 101) != CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)" );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt =null;

            //APPROVING THE SHIPPED LINE ITEMS
            pstmt = conn.prepareStatement ( "UPDATE SALES_HOLD_TABLE SET HOLD_REASON_CODE = '', IS_APPROVED = 0 WHERE LINEITEM_ID IN (SELECT LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE CONVERT(VARCHAR, [TIMESTAMP], 101) != CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101))" );
            pstmt.executeUpdate();

            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not save sales orders", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( !success )
                {
                    try{ conn.rollback(); } catch ( SQLException ignore ){}
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
    }

    public static void saveSalesOrderLineItem ( DBTransaction dbTransaction, SalesParserLineItem lineItem ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int portingStatusCode = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( "{CALL SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}" );

            cstmt.setString ( 1, lineItem.getSalesOrderNo() );
            cstmt.setString ( 2, lineItem.getCustomerNumber() ); //
            cstmt.setString ( 3, (lineItem.getCustomerPO() == null || lineItem.getCustomerPO().trim().length() == 0) ? lineItem.getSalesOrderNo() : lineItem.getCustomerPO() );
            cstmt.setString ( 4, lineItem.getShippingMethod() );
            cstmt.setString ( 5, lineItem.getDeliveryTerms() );
            cstmt.setInt ( 6, lineItem.getLineItemNo() );
            cstmt.setInt ( 7, lineItem.getShipmentNo() );
            cstmt.setString ( 8, lineItem.getMarketingPartNo() ); //
            cstmt.setInt ( 9, lineItem.getQuantity() );
            cstmt.setString ( 10, StringUtils.formatAmount( lineItem.getUnitPrice(), "##########.###") );
            cstmt.setString( 11, lineItem.getRequestedDockDate() );
            cstmt.setString ( 12, (lineItem.getScheduleDate() == null || lineItem.getScheduleDate().trim().length() == 0 ) ? null : lineItem.getScheduleDate().trim() );
            cstmt.setString ( 13, (lineItem.getBookingDate() == null || lineItem.getBookingDate().trim().length() == 0 ) ? null : lineItem.getBookingDate().trim() );
            cstmt.setString ( 14, lineItem.getCustomerPartNo() );
            cstmt.setString ( 15, lineItem.getShippingLocation() );
            cstmt.setString ( 16, lineItem.getEndUser() );
            cstmt.setString( 17, lineItem.getEndCustomer() );
            cstmt.setString ( 18, lineItem.getTransactionDate() );
            cstmt.setString ( 19, lineItem.getCustomerPORev() );
            cstmt.setString ( 20, lineItem.getPaymentTerms() );
            cstmt.setString( 21, (lineItem.getRevisedScheduleDate() == null || lineItem.getRevisedScheduleDate().trim().length() == 0 ) ? null : lineItem.getRevisedScheduleDate().trim() );

            cstmt.registerOutParameter( 22, Types.INTEGER );
            cstmt.setString( 23, lineItem.getSalesPerson() );
            cstmt.setInt ( 24, lineItem.getIsSample() );
            cstmt.setString ( 25, (lineItem.getShippedDate() == null || lineItem.getShippedDate().trim().length() == 0 ) ? null : lineItem.getShippedDate().trim() );
            cstmt.setString ( 26, lineItem.getDescription() );
            Address shipAddress = (lineItem.getShipAddress() == null ? new Address() : lineItem.getShipAddress());
            cstmt.setString ( 27, shipAddress.getAddress1() );
            cstmt.setString ( 28, shipAddress.getAddress2() );
            cstmt.setString ( 29, shipAddress.getAddress3() );
            cstmt.setString ( 30, shipAddress.getCity() );
            cstmt.setString ( 31, shipAddress.getState() );
            cstmt.setString ( 32, shipAddress.getZipCode() );
            cstmt.setString ( 33, shipAddress.getCountry() );
            cstmt.execute();
            portingStatusCode = cstmt.getInt( 22 );
            lineItem.setPortingStatusCode( portingStatusCode );

            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not save sales order Line Item", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( !success )
                {
                    try{ conn.rollback(); } catch ( SQLException ignore ){}
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
    }

    public static void saveSalesShippedOrders ( DBTransaction dbTransaction, Iterator<SalesParserLineItem> salesShipentItr ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        SalesParserLineItem lineItem = null;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall ( "{ CALL SP_SAVE_SALES_ORDER_LINE_ITEM_SHIP_INFO (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            while ( salesShipentItr.hasNext() )
            {
                lineItem = (SalesParserLineItem)salesShipentItr.next();

                cstmt.setString ( 1, lineItem.getSalesOrderNo() );
                cstmt.setString ( 2, lineItem.getLabel() );
                cstmt.setInt ( 3, lineItem.getLineItemNo() );
                cstmt.setInt ( 4, lineItem.getShipmentNo() );
                cstmt.setInt ( 5, lineItem.getShippedQty() );
                cstmt.setString ( 6, (lineItem.getShippedDate() == null || lineItem.getShippedDate().trim().length() == 0 ) ? null : lineItem.getShippedDate().trim() );
                cstmt.setString ( 7, lineItem.getStatus() );
                cstmt.setString ( 8, lineItem.getShippingLocation() );
                cstmt.setString( 9, lineItem.getCustomerName() );
                cstmt.setString( 10, lineItem.getCustomerPO() );
                cstmt.setString( 11, lineItem.getManufacturingPartNo() );
                cstmt.setFloat( 12, lineItem.getUnitPrice() );
                cstmt.setString( 13, lineItem.getBookingDate() );
                cstmt.setString( 14, lineItem.getScheduleDate() );
                cstmt.setString( 15, lineItem.getRevisedScheduleDate() );
                cstmt.setString( 16, lineItem.getRequestedDockDate() );
                cstmt.setInt( 17, lineItem.getIsSample() );
                cstmt.setString( 18, lineItem.getShipReqNo());
                cstmt.execute();
            }

            if ( dbTransaction == null )
            {

                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not save sales shipping orders", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( !success )
                {
                    try{ conn.rollback(); } catch ( SQLException ignore ){}
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
            }

            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
    }

    public static void saveSalesOrdersAkros(DBTransaction dbTransaction, ArrayList<SalesParserLineItem> lineItemList) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int lineItemListSize = (lineItemList == null) ? 0 : lineItemList.size();
        SalesParserLineItem lineItem = null;
        int portingStatusCode = 0;
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall("{CALL SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM_AKROS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            for (int i = 0; i < lineItemListSize; i++) {
                lineItem = lineItemList.get(i);
                if (lineItem.getPortingStatusCode() != -1) {
                    cstmt.setString (1, lineItem.getSalesOrderNo());
                    cstmt.setString (2, lineItem.getCustomerNumber()); //
                    cstmt.setString (3, (lineItem.getCustomerPO() == null || lineItem.getCustomerPO().trim().length() == 0) ? lineItem.getSalesOrderNo() : lineItem.getCustomerPO());
                    cstmt.setInt    (4, lineItem.getLineItemNo());
                    cstmt.setString (5, lineItem.getManufacturingPartNo());
                    cstmt.setInt    (6, lineItem.getQuantity());
                    cstmt.setString (7, StringUtils.formatAmount(lineItem.getUnitPrice(), "##########.###"));
                    cstmt.setString (8, lineItem.getRequestedDockDate());
                    cstmt.setString (9, (lineItem.getScheduleDate() == null || lineItem.getScheduleDate().trim().length() == 0) ? null : lineItem.getScheduleDate().trim());
                    cstmt.setString (10, (lineItem.getBookingDate() == null || lineItem.getBookingDate().trim().length() == 0) ? null : lineItem.getBookingDate().trim());
                    cstmt.setString (11, lineItem.getCustomerPartNo());
                    cstmt.setString (12, lineItem.getEndCustomer());
                    cstmt.setString (13, lineItem.getPaymentTerms());
                    cstmt.setString (14, (lineItem.getRevisedScheduleDate() == null || lineItem.getRevisedScheduleDate().trim().length() == 0) ? null : lineItem.getRevisedScheduleDate().trim());
                    cstmt.registerOutParameter(15, Types.INTEGER);
                    cstmt.setInt    (16, lineItem.getIsSample());
                    cstmt.setString (17, lineItem.getDescription() != null && lineItem.getDescription().length() > 200 ? lineItem.getDescription().substring(0,200) : lineItem.getDescription() );
                    cstmt.setString (18, lineItem.getCustomerName());
                    cstmt.setString (19, lineItem.getStatus());
                    cstmt.setInt    (20, lineItem.getInternalPOLineNo());
                    cstmt.setInt    (21, lineItem.isPendingShipment() ? 0 : 1);
                    cstmt.setString (22, lineItem.getShipToTagName());
                    cstmt.setString (23, lineItem.getShippingMethod());
                    cstmt.setString (24, lineItem.getShippedDate());
                    cstmt.setString (25, lineItem.getShippingLocation());
                    cstmt.execute();
                    portingStatusCode = cstmt.getInt(15);
                    lineItem.setPortingStatusCode(portingStatusCode);
                }
            }
            if (dbTransaction == null) {
                if (conn != null) {
                    conn.commit();
                }
            }
            success = true;
        }
        catch (SQLException e) {
            throw new ScalarException("Could not save sales orders", e);
        }
        finally {
            if (dbTransaction == null) {
                if (!success) {
                    try {
                        conn.rollback();
                    } catch (SQLException ignore) {
                    }
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                    cstmt = null;
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public static void saveSalesOrdersBeceem(DBTransaction dbTransaction, ArrayList<SalesParserLineItem> lineItemList) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int lineItemListSize = (lineItemList == null) ? 0 : lineItemList.size();
        SalesParserLineItem lineItem = null;
        int portingStatusCode = 0;
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall("{CALL SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM_BECEEM(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            for (int i = 0; i < lineItemListSize; i++)
            {
                lineItem = lineItemList.get(i);
                if (lineItem.getPortingStatusCode() != -1)
                {
                    int p = 1;
                    cstmt.setString (p++, lineItem.getSalesOrderNo());
                    cstmt.setString (p++, lineItem.getCustomerNumber());
                    cstmt.setString (p++, lineItem.getCustomerName());
                    cstmt.setString (p++, (lineItem.getCustomerPO() == null || lineItem.getCustomerPO().trim().length() == 0) ? lineItem.getSalesOrderNo() : lineItem.getCustomerPO());
                    cstmt.setString (p++, (lineItem.getBookingDate() == null || lineItem.getBookingDate().trim().length() == 0) ? null : lineItem.getBookingDate().trim());
                    cstmt.setString (p++, lineItem.getManufacturingPartNo());
                    cstmt.setString (p++, lineItem.getDescription() );
                    cstmt.setInt    (p++, lineItem.getQuantity());
                    cstmt.setString (p++, lineItem.getRequestedDockDate());
                    cstmt.setString (p++, StringUtils.formatAmount(lineItem.getUnitPrice(), "##########.###"));
                    cstmt.setInt    (p++, lineItem.getLineItemNo());
                    cstmt.setString (p++, lineItem.getShipAddress().getAddress1());
                    cstmt.setString (p++, lineItem.getShipAddress().getAddress2());
                    cstmt.setString (p++, lineItem.getShipAddress().getAddress3());
                    cstmt.setString (p++, lineItem.getShipAddress().getCity());
                    cstmt.setString (p++, lineItem.getShipAddress().getState());
                    cstmt.setString (p++, lineItem.getShipAddress().getCountry());
                    cstmt.setString (p++, lineItem.getShipAddress().getZipCode());
                    cstmt.registerOutParameter(p++, Types.INTEGER);

                    cstmt.execute();
                    portingStatusCode = cstmt.getInt( 19 );
                    lineItem.setPortingStatusCode(portingStatusCode);
                }
            }
            if (dbTransaction == null) {
                if (conn != null) {
                    conn.commit();
                }
            }
            success = true;
        }
        catch (SQLException e) {
            throw new ScalarException("Could not save sales orders", e);
        }
        finally {
            if (dbTransaction == null) {
                if (!success) {
                    try {
                        conn.rollback();
                    } catch (SQLException ignore) {
                    }
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                    cstmt = null;
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException ignore) {
                }
            }
        }
    }

}
